import 'package:flutter/foundation.dart';
import 'package:mybase/base/tool/db/FFSqlManager.dart';
import 'package:mybase/base/tool/db/MyBatch.dart';
import 'package:mybase/base/tool/empty/FFEmpty.dart';
import 'package:mybase/base/tool/entitylog/MyLog.dart';
import 'package:mybase/base/tool/extension/FFListExt.dart';
import 'package:mybase/base/tool/extension/FFMapExt.dart';
import 'package:mybase/base/tool/extension/FFStringExt.dart';
import 'package:mybase/base/tool/globalMethod/FFSafeValue.dart';
import 'package:mybase/base/tool/language/baseLanguage/objs/en.dart';
import 'package:mybase/base/tool/pprint/FFLog.dart';
import 'package:mybase/base/tool/time/FFTime.dart';
import 'package:sqflite/sqflite.dart';
import 'FFsql.dart';

/**
 * 抽象类，不用动
 */
class FFDefaultSql implements FFsql {
  // 时间降序
  String updateTimeDesc() {
    return "${getUpdateTime()} desc";
  }

  // 时间升序
  String updateTimeAsc() {
    return "${getUpdateTime()} asc";
  }

  ///TODO 以下方法必须实现
  @override
  String getTableName() {
    throw UnimplementedError("这个方法必须实现 getTableName");
  }

  @override
  FFSQlEntity mapToEntity(Map<String, dynamic> map) {
    // var entity = DemoEntity.fromJson(map);
    // entity.superColumn(entity, map); //此句一定要调用
    // return entity;
    throw UnimplementedError("这个方法必须实现 mapToEntity");
  }

  ///TODO 以下方法可选
  @override
  String getPrimaryKeyName() {
    return "sqlid";
  }

  @override
  String getCreateTime() {
    return "createtime";
  }

  @override
  String getUpdateTime() {
    return "updatetime";
  }

  @override
  Map<String, dynamic> entityToMap(FFSQlEntity entity) {
    Map<String, dynamic> map = entity.toJson();
    map.safeInsert(getCreateTime(), FFTime.nowSecond());
    map.safeInsert(getUpdateTime(), FFTime.nowSecond());
    return map;
  }

  @override
  String setCreateSql(
    String tableName,
    List<FFSQlColumn> columns, {
    String? constraint,
  }) {
    List<String> list = [];
    list.add("CREATE TABLE IF NOT EXISTS $tableName (");
    list.add("${getPrimaryKeyName()} INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,");

    for (int i = 0; i < columns.length; i++) {
      FFSQlColumn column = columns[i];
      String info = column.info;

      switch (column.type) {
        case FFSQlColumnType.INTEGER:
          list.add("${column.name} INTEGER $info,");
          break;
        case FFSQlColumnType.TEXT:
          list.add("${column.name} TEXT $info,");
          break;
        case FFSQlColumnType.REAL:
          list.add("${column.name} REAL $info,");
          break;
        case FFSQlColumnType.BLOB:
          list.add("${column.name} BLOB $info,");
          break;
        default:
          print("FFDefaultSql 未处理的类型");
      }
    }

    list.add("${getCreateTime()} INTEGER,");

    if (FFEmpty.stringIsEmpty(constraint)) {
      list.add("${getUpdateTime()} INTEGER");
    } else {
      list.add("${getUpdateTime()} INTEGER,");
      list.add(constraint ?? "");
    }
    list.add(");");

    String sql = list.join("\n");
    return sql;
  }

  // 删除
  Future<bool> deleteBySqlid({int? sqlid}) async {
    if (sqlid == null) return Future(() => false);

    String? whereSql = "${getPrimaryKeyName()}=?";
    List<dynamic>? whereArgsSql = [sqlid];

    await FFSqlManager().open();
    bool isSuccess = false;
    try {
      await FFSqlManager().db?.transaction((txn) async {
        int row = await txn.delete(
          getTableName(),
          where: whereSql,
          whereArgs: whereArgsSql,
        );
        isSuccess = row > 0;
      });
    } catch (e) {
      FFLog.error("操作catch ${e.toString()}");
    }

    if (kDebugMode) FFLog.result(isSuccess, "表删除数据");
    return isSuccess;
  }

  @override
  Future<bool> delete({
    int? sqlid,
    String? where,
    List<dynamic>? whereArgs,
  }) async {
    String? whereSql;
    List<dynamic>? whereArgsSql;

    if (sqlid != null) {
      whereSql = "${getPrimaryKeyName()}=?";
      whereArgsSql = [sqlid];
    } else {
      whereSql = where;
      whereArgsSql = whereArgs;
    }

    await FFSqlManager().open();

    bool isSuccess = false;
    try {
      await FFSqlManager().db?.transaction((txn) async {
        int row = await txn.delete(
          getTableName(),
          where: whereSql,
          whereArgs: whereArgsSql,
        );
        isSuccess = row > 0;
      });
    } catch (e) {
      FFLog.error("操作catch ${e.toString()}");
    }

    if (kDebugMode) FFLog.result(isSuccess, "表删除数据");
    return isSuccess;
  }

  // 批量删除
  Future<bool> deleteBatchBySqlId({
    required List<int> list,
  }) async {
    if (FFEmpty.listIsEmpty(list)) {
      return Future(() => false);
    }
    await FFSqlManager().open();

    try {
      await FFSqlManager().db?.transaction((txn) async {
        Batch batch = txn.batch();
        for (int sqlid in list) {
          batch.delete(getTableName(), where: "${getPrimaryKeyName()}=?", whereArgs: [sqlid]);
        }
        await batch.commit();
      });

      return true;
    } catch (e) {
      FFLog.error("操作catch ${e.toString()}");
    }

    return false;
  }

  // 批量插入
  Future<List<Object?>?> insertBatchByEntity({
    required List<FFSQlEntity> list,
    bool? ignoreError,
  }) async {
    var list2 = list.map((e) => e.toJson()).toList();
    return await insertBatchByMap(
      list: list2,
      ignoreError: ignoreError,
    );
  }

  // 批量插入
  Future<List<Object?>?> insertBatchByMap({
    required List<Map<String, dynamic>>? list,
    bool? ignoreError,
  }) async {
    if (FFEmpty.listIsEmpty(list)) return null;

    await FFSqlManager().open();

    try {
      return await FFSqlManager().db?.transaction((txn) async {
        Batch batch = txn.batch();
        for (var map in list!) {
          bool hasIDKey = map.containsKey(getPrimaryKeyName());
          if (hasIDKey) {
            ///更新
            int sqLid = "${map[getPrimaryKeyName()]}".toINT(defaultValue: -1);
            if (sqLid == -1) {
              assert(false, "insertByMap错误：${getPrimaryKeyName()} 不能解析为数字");
              return null;
            }

            map.safeInsert(getUpdateTime(), FFTime.nowSecond());
            map.remove(getPrimaryKeyName());
            map.remove(getCreateTime());

            batch.update(getTableName(), map, where: "${getPrimaryKeyName()}=?", whereArgs: [sqLid]);
          } else {
            ///插入
            map.safeInsert(getCreateTime(), FFTime.nowSecond());
            map.safeInsert(getUpdateTime(), FFTime.nowSecond());

            batch.insert(
              getTableName(),
              map,
              conflictAlgorithm: ignoreError == true ? ConflictAlgorithm.ignore : null,
            );
          }
        }
        return await batch.commit();
      });
    } catch (e) {
      FFLog.error("操作catch ${e.toString()}");
    }

    return null;
  }

  @override
  Future<int> insert({required FFSQlEntity entity}) async {
    await FFSqlManager().open();

    Map<String, dynamic> dic = entityToMap(entity);
    return insertByMap(map: dic);
  }

  // 插入
  Future<int> insertByMap({
    required Map<String, dynamic> map,
  }) async {
    int id = -1;
    try {
      bool hasIDKey = map.containsKey(getPrimaryKeyName());
      await FFSqlManager().db?.transaction((txn) async {
        if (hasIDKey) {
          ///更新
          int sqLid = "${map[getPrimaryKeyName()]}".toINT(defaultValue: -1);
          if (sqLid == -1) {
            FFLog.error("insertByMap错误：${getPrimaryKeyName()} 不能解析为数字");
            return id;
          }

          map.safeInsert(getUpdateTime(), FFTime.nowSecond());
          map.remove(getPrimaryKeyName());
          map.remove(getCreateTime());

          id = await txn.update(getTableName(), map, where: "${getPrimaryKeyName()}=?", whereArgs: [sqLid]);
        } else {
          ///插入
          map.safeInsert(getCreateTime(), FFTime.nowSecond());
          map.safeInsert(getUpdateTime(), FFTime.nowSecond());

          id = await txn.insert(getTableName(), map);
        }
      });
    } catch (e) {
      FFLog.error("操作catch ${e.toString()}");
    }

    if (kDebugMode) FFLog.result(id > 0, "表插入数据");
    return id;
  }

  // 此时已经从表中获取到数据，如果你想改一下，就在这儿操作
  adjustEntity() {}

  // 关联查询
  Future<void> associateEntity() async {}

  // 原始查询
  Future<List<Map<String, dynamic>>?> rawQuery(String sql, [List<Object?>? arguments]) async {
    await FFSqlManager().open();
    return await FFSqlManager().db?.rawQuery(sql, arguments);
  }

  //假设limit为: 100,那么offset: (page - 1) * 100
  //page从1开始
  @override
  Future<List<T>> query<T>({
    int? sqlid,
    int? limit,
    int? offset,
    String? orderby,
    String? where,
    List<dynamic>? whereArgs,
    bool isAdjust = true,
    bool isAssociate = false,
    Function(T e)? onReady,
  }) async {
    List<T> results = [];
    await FFSqlManager().open();

    if (sqlid != null) {
      where = "${getPrimaryKeyName()}=?";
      whereArgs = [sqlid];
    }

    if (kDebugMode) {
      FFLog.info(
          "查询参数 主键:$sqlid, offset:$offset, limit:$limit, where:$where, whereArgs:$whereArgs, orderby:$orderby, isAdjust:$isAdjust, isAssociate:$isAssociate");
    }

    //await FFSqlManager().db?.transaction((txn) async {});
    List<Map<String, dynamic>>? list = await FFSqlManager().db?.query(
          getTableName(),
          where: where,
          whereArgs: whereArgs,
          orderBy: orderby,
          limit: limit,
          offset: offset,
        );

    if (FFEmpty.listIsEmpty(list)) {
      return results;
    }

    for (int i = 0; i < list.toLength; i++) {
      Map<String, dynamic> map = list![i];
      if (kDebugMode) {
        Mylog.p(url: "数据表${getTableName()}", result: map);
      }
      dynamic entity = mapToEntity(map);
      // 调整一下
      if (isAdjust) await entity.adjustEntity();
      // 查一下关联的数据
      if (isAssociate) await entity.associateEntity();
      // 回调
      onReady?.call(entity);
      results.add(entity);
    }

    return results;
  }

  // 查询 page从1开始
  Future<List<T>> query2<T>({
    int page = 1,
    int? limit,
    int? sqlid,
    String? orderby,
    String? where,
    List<dynamic>? whereArgs,
    bool isAdjust = true,
    bool isAssociate = false,
    Function(T e)? onReady,
  }) {
    int? offset;
    if (limit != null) {
      offset = (page - 1) * limit;
    }

    return query(
      sqlid: sqlid,
      limit: limit,
      offset: offset,
      orderby: orderby,
      where: where,
      whereArgs: whereArgs,
      isAdjust: isAdjust,
      isAssociate: isAssociate,
      onReady: onReady,
    );
  }

  // 查询 只取第一个
  Future<T?> queryOne<T>({
    int? sqlid,
    String? where,
    String? orderby,
    List<dynamic>? whereArgs,
    Function(T e)? onReady,
    bool isAdjust = true,
    bool isAssociate = false,
  }) async {
    List<T> list = await query2<T>(
      page: 1,
      limit: 1,
      sqlid: sqlid,
      orderby: orderby,
      where: where,
      whereArgs: whereArgs,
      onReady: onReady,
      isAdjust: isAdjust,
      isAssociate: isAssociate,
    );

    return globalSafeValueFromList<T>(list, 0);
  }

  @override
  Future<bool> updateByEntity({
    required int? sqlid,
    required FFSQlEntity entity,
  }) async {
    Map<String, dynamic> dic = entityToMap(entity);
    return await updateByMap(sqlid: sqlid, map: dic);
  }

  // 更新
  Future<bool> updateByMap({
    required int? sqlid,
    required Map<String, dynamic> map,
  }) async {
    if (sqlid == null || sqlid <= 0) {
      return Future(() => false);
    }

    if (FFEmpty.mapIsEmpty(map)) {
      return Future(() => false);
    }

    map.remove(getPrimaryKeyName());
    map.remove(getCreateTime());
    map.safeInsert(getUpdateTime(), FFTime.nowSecond());

    await FFSqlManager().open();
    int id = 0;
    try {
      await FFSqlManager().db?.transaction((txn) async {
        id = await txn.update(getTableName(), map, where: "${getPrimaryKeyName()}=?", whereArgs: [sqlid]);
      });
    } catch (e) {
      FFLog.error("操作catch ${e.toString()}");
    }

    if (kDebugMode) FFLog.result(id > 0, "表更新数据");
    return id > 0;
  }

  @override
  Future<bool> updateBatchByEntity({
    required List<FFSQlEntity> list,
  }) {
    var list2 = list.map((e) => e.toJson()).toList();
    return updateBatchByMap(list: list2);
  }

  @override
  Future<bool> updateBatchByMap({
    required List<Map<String, dynamic>> list,
  }) async {
    if (FFEmpty.listIsEmpty(list)) {
      return Future(() => false);
    }
    await FFSqlManager().open();

    try {
      await FFSqlManager().db?.transaction((txn) async {
        Batch batch = txn.batch();
        for (var map in list) {
          if (!map.containsKey(getPrimaryKeyName())) {
            FFLog.error("updateBatchByMap错误：${getPrimaryKeyName()} = null");
            continue;
          }
          int sqLid = "${map[getPrimaryKeyName()]}".toINT(defaultValue: -1);
          if (sqLid == -1) {
            FFLog.error("updateBatchByMap错误：${getPrimaryKeyName()} 不能解析为数字");
            continue;
          }

          map.remove(getPrimaryKeyName());
          map.remove(getCreateTime());
          map.safeInsert(getUpdateTime(), FFTime.nowSecond());

          batch.update(getTableName(), map, where: "${getPrimaryKeyName()}=?", whereArgs: [sqLid]);
        }
        await batch.commit();
      });
      return true;
    } catch (e) {
      FFLog.error("操作catch ${e.toString()}");
    }

    return false;
  }

  @override
  Future<List<Object?>?> batchCustom({
    required Function(MyBatch myBatch) onCustom,
  }) async {
    await FFSqlManager().open();

    try {
      return await FFSqlManager().db?.transaction((txn) async {
        Batch batch = txn.batch();
        MyBatch b = MyBatch(batch: batch);
        onCustom(b);
        return await batch.commit();
      });
    } catch (e) {
      FFLog.error("操作 batchCustom catch ${e.toString()}");
    }

    return null;
  }

  @override
  Future<void> addColumn({
    required String columnName,
    required FFSQlColumnType type,
  }) async {
    String sqlColumnType = "TEXT";
    switch (type) {
      case FFSQlColumnType.INTEGER:
        sqlColumnType = "INTEGER";
        break;
      case FFSQlColumnType.TEXT:
        sqlColumnType = "TEXT";
        break;
      case FFSQlColumnType.REAL:
        sqlColumnType = "REAL";
        break;
      case FFSQlColumnType.BLOB:
        sqlColumnType = "BLOB";
        break;
    }

    try {
      await FFSqlManager().db?.transaction((txn) async {
        String sql = "alter table ${getTableName()} add column $columnName $sqlColumnType";
        await txn.execute(sql);
      });
    } catch (e) {}
  }

  @override
  Future<int> getTableCount() async {
    try {
      return await FFSqlManager().db?.transaction((txn) async {
            var result = await txn.rawQuery('SELECT COUNT(*) FROM ${getTableName()}');
            int count = Sqflite.firstIntValue(result) ?? 0;
            return count;
          }) ??
          0;
    } catch (e) {}
    return 0;
  }
}
